package com.ma4567.remotecontroller.MysqlHelper;

import com.ma4567.remotecontroller.UserManager.User;
import com.ma4567.remotecontroller.util.Matters;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class CalendarTodo {
    public static void Init() throws SQLException {
        Connection conn = ConnectPool.getConnection();
        PreparedStatement stmt = conn.prepareStatement(
                "create table if not exists calendar(" +
                        "    id      int auto_increment primary key," +
                        "    user_id int not null," +
                        "    year    int not null," +
                        "    month   int null," +
                        "    day     int not null," +
                        "    time    text not null," +
                        "    matter  text not null," +
                        "    constraint calendar_user_Account_fk" +
                        "    foreign key (user_id) references user (Account))" +
                        "    character set utf8 engine InnoDB;");
        stmt.executeUpdate();
        conn.close();
    }

    public static ArrayList<Matters> getMattersByDate(int user, int year, int month, int day) throws SQLException {
        Connection conn = ConnectPool.getConnection();
        ArrayList<Matters> result = new ArrayList<>();

        PreparedStatement stmt = conn.prepareStatement("select * from calendar where user_id = ? and year = ? and month = ? and day = ?;");
        stmt.setInt(1, user);
        stmt.setInt(2, year);
        stmt.setInt(3, month);
        stmt.setInt(4, day);

        ResultSet rs = stmt.executeQuery();
        while(rs.next()){
            result.add(new Matters(rs.getString("time"), rs.getString("matter"), rs.getInt("id"))) ;
        }

        conn.close();
        return result;
    }

    public static void addMatters(int user, int year, int month, int day, String time, String matter) throws SQLException {
        Connection conn = ConnectPool.getConnection();

        PreparedStatement stmt = conn.prepareStatement("insert into calendar (user_id, year, month, day, time, matter) values (?,?,?,?,?,?);");
        stmt.setInt(1, user);
        stmt.setInt(2, year);
        stmt.setInt(3, month);
        stmt.setInt(4, day);
        stmt.setString(5, time);
        stmt.setString(6, matter);

        stmt.executeUpdate();

        conn.close();
    }

    public static void deleteMatter(int id) throws SQLException {
        Connection conn = ConnectPool.getConnection();

        PreparedStatement stmt = conn.prepareStatement("delete from calendar where id = ?;");
        stmt.setInt(1, id);

        stmt.executeUpdate();

        conn.close();
    }
}
